package dao.mysql;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.UserDTO;
import dao.DAOException;
import dao.UserDAO;

public class UserDAOImpl implements UserDAO {
	
	private static final String dbUrl = "jdbc:mysql://localhost:3306/facilitybookingsystem";
	private static final String dbUserName = "root";
	private static final String dbPassword = "password";
	
	public void insertUser(UserDTO user) throws DAOException
	{
		String insertSql = "INSERT INTO USER (Username,UPassword,Role,Email,Phone,ManagerID)VALUES('" + user.getUserName()+ "', '" + user.getUserPW() +
				"', '" + user.getRole()+"', '" +user.getEmail()+"', '" +user.getPhone()
				+"', '" +user.getManagerID()+"');";
				try {
					Class.forName("com.mysql.jdbc.Driver");
				} catch (ClassNotFoundException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
				Connection conn = null;
				try {
					conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
					Statement st = conn.createStatement();
					Logger.getLogger(getClass().getName()).log(Level.INFO,
							"Executing insert: " + insertSql);
					st.executeUpdate(insertSql);
					st.close();
				} catch (Exception e) {
					String error = "Error selecting users. Nested Exception is: " + e;
					Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
					throw new DAOException(error);
				} finally {
					try {
						conn.rollback();
						conn.close();
					} catch (Exception e) {
					}
				}	
	}
	
	public void deleteUser(UserDTO user)throws DAOException {
			String deleteSql = "DELETE FROM user WHERE UserID="
					+ user.getUserID() + ";";
			try {
				Class.forName("com.mysql.jdbc.Driver");
			} catch (ClassNotFoundException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			Connection conn = null;
			try {
				conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
				Statement st = conn.createStatement();
				Logger.getLogger(getClass().getName()).log(Level.INFO,
						"Executing delete: " + deleteSql);
				st.executeUpdate(deleteSql);
				st.close();
			} catch (Exception e) {
				String error = "Error deleting users. Nested Exception is: " + e;
				System.out.println("#$#$#@$#@#@$#@$#@$#$#@");
				Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
				throw new DAOException(error);
			} finally {
				try {
					conn.rollback();
					conn.close();
				} catch (Exception e) {
				}
			}
	}
	
	public void update(UserDTO user) throws DAOException
	{
		String updateSql = "UPDATE facilitybookingsystem.user SET Username = '"
				+ user.getUserName() + "', UPassword = '" + user.getUserPW()+ "', Role='" + 
				user.getRole()+ "',ManagerID =" + user.getManagerID() + ", Email= '" +  user.getEmail()+"',Phone = '"+user.getPhone()
				+ "' WHERE UserID=" + user.getUserID() + ";";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing update: " + updateSql);
			st.executeUpdate(updateSql);
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}

	}
	
	public ArrayList<UserDTO> findAllUsers()throws DAOException{
		ArrayList<UserDTO> users = new ArrayList<UserDTO>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String selectSql = "SELECT * FROM facilitybookingsystem.user;";
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				UserDTO oneUser = new UserDTO();
				oneUser.setUserID(rs.getInt("UserID"));
				oneUser.setUserName(rs.getString("Username"));
				oneUser.setUserPW(rs.getString("UPassword"));
				oneUser.setRole(rs.getString("Role"));
				oneUser.setEmail(rs.getString("Email"));
				oneUser.setPhone(rs.getString("Phone"));
				oneUser.setManagerID(rs.getInt("ManagerID"));
				users.add(oneUser);
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return users;
	}
	public UserDTO findUser(int userID) throws DAOException {
		String selectSql = "SELECT * FROM user WHERE UserID=" + userID
				+ ";";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		UserDTO oneUser = new UserDTO();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				oneUser.setUserID(rs.getInt("UserID"));
				oneUser.setUserName(rs.getString("Username"));
				oneUser.setUserPW(rs.getString("UPassword"));
				oneUser.setRole(rs.getString("Role"));
				oneUser.setEmail(rs.getString("Email"));
				oneUser.setPhone(rs.getString("Phone"));
				oneUser.setManagerID(rs.getInt("ManagerID"));
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		System.out.println(oneUser.toString());
		return oneUser;
	}
		
	public UserDTO findUserByManagerId(int mgrID) throws DAOException
	{
		String selectSql = "SELECT userName, phone, email, role, managerID FROM user WHERE managerID='" + mgrID
				+ "';";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
		
			e1.printStackTrace();
		}
		UserDTO user = new UserDTO();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
			user.setEmail(rs.getString("email"));
			user.setManagerID(Integer.parseInt(rs.getString("managerID")));
			user.setPhone(rs.getString("phone"));
			user.setRole(rs.getString("role"));
			//user.setUserID(rs.getShort("userID"));
			user.setUserName(rs.getString("userName"));
			
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		System.out.println(user.toString());
		return user;
	}
	
	public UserDTO authenticate(String userName, String password) throws DAOException
	{
		UserDTO user1 = new UserDTO();
		ArrayList<UserDTO> list = new ArrayList<UserDTO>();
		
		String selectSql = "SELECT * FROM user WHERE Username='" + userName + "' AND " +
		"Upassword ='" + password + "'";
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
			user1.setUserPW(rs.getString("Upassword"));
			user1.setUserID(Integer.parseInt(rs.getString("UserID")));
			user1.setUserName(rs.getString("userName"));
			user1.setEmail(rs.getString("Email"));
			user1.setPhone(rs.getString("Phone"));
			user1.setRole(rs.getString("Role"));
			list.add(user1);
			
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		System.out.println(user1.toString());
		return user1;
	}

}
